﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Lm.Common.DAL.ModelCodeGenerator
{
    class SQLTableInfoReader : ITableInfoReader
    {
        protected string ConnectionString { get; set; }

        protected SQLTableInfoReader()
        { }

        public SQLTableInfoReader(string server, string db, string account, string password)
        {
            //Data Source=bds-010.hichina.com;Initial Catalog=bds0100244_db;User ID=bds0100244
            var conBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
            conBuilder.DataSource = server;
            conBuilder.InitialCatalog = db;
            conBuilder.UserID = account;
            conBuilder.Password = password;
            this.ConnectionString = conBuilder.ToString();
        }

        public System.Data.DataTable Read()
        {
            using (var con = new System.Data.SqlClient.SqlConnection(this.ConnectionString))
            {
                var sql = " \n"
                           + "select  \n"
                           + "    [表名]=c.Name, \n"
                           + "    [表说明]=isnull(f.[value],''), \n"
                           + "    [列名]=a.Name, \n"
                           + "    [列序号]=a.Column_id, \n"
                           + "    [标识]=case when is_identity=1 then '√' else '' end, \n"
                           + "    [主鍵]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N'PK' and name in \n"
                           + "                    (select Name from sys.indexes where index_id in \n"
                           + "                    (select indid from sysindexkeys where  colid=a.column_id))) \n"
                           + "                    then '√' else '' end, \n"
                           + "    [类型]=b.Name, \n"
                           + "    [字节数]=case when a.[max_length]=-1 and b.Name!='xml' then 'max/2G'  \n"
                           + "            when b.Name='xml' then ' 2^31-1字節/2G' \n"
                           + "            else rtrim(a.[max_length]) end, \n"
                           + "    [长度]=ColumnProperty(a.object_id,a.Name,'Precision'), \n"
                           + "    [小数]=isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0), \n"
                           + "    [是否为空]=case when a.is_nullable=1 then '√' else '' end, \n"
                           + "    [列说明]=isnull(e.[value],''), \n"
                           + "    [默认值]=isnull(d.text,'')     \n"
                           + "from  \n"
                           + "    sys.columns a \n"
                           + "left join \n"
                           + "    sys.types b on a.user_type_id=b.user_type_id \n"
                           + "inner join \n"
                           + "    sys.objects c on a.object_id=c.object_id and c.Type='U' \n"
                           + "left join \n"
                           + "    syscomments d on a.default_object_id=d.ID \n"
                           + "left join \n"
                           + "    sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1  \n"
                           + "left join \n"
                           + "    sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1 \n"
                           + "ORDER BY c.name,a.column_id; \n"
                           + "";
                con.Open();
                using (var apt = new System.Data.SqlClient.SqlDataAdapter(sql, con))
                {
                    var dt = new System.Data.DataTable();
                    apt.Fill(dt);
                    return dt;
                }
            }
        }
    }
}
